from pymysql import Connection
from datetime import datetime


# 数据库连接
def connect():
    return Connection(
        host='localhost', port=3306, user='root',
        passwd='root', database='db3', charset='utf8', autocommit=True
    )


# 创建表并初始化
def createTable():
    db = connect()
    cursor = db.cursor()

    # 创建银行表
    cursor.execute("""
        create table if not exists t_rank(
            id int primary key auto_increment,
            r_name varchar(20))
    """)

    # 创建银行卡表
    cursor.execute("""
        create table if not exists t_card(
            id int primary key auto_increment,
            username varchar(20),
            card_no  varchar(20),
            create_time datetime,
            money double,
            r_id int)
    """)

    # 初始化银行数据
    cursor.execute("SELECT COUNT(*) FROM t_rank")
    if cursor.fetchone()[0] == 0:
        banks = ['工商银行', '建设银行', '农业银行', '中国银行', '招商银行']
        for bank in banks:
            cursor.execute("INSERT INTO t_rank (r_name) VALUES (%s)", (bank,))

    db.close()


# 添加银行卡
def add_card(username, card_no, money, r_id):
    db = connect()
    cursor = db.cursor()
    cursor.execute(
        "INSERT INTO t_card (username, card_no, create_time, money, r_id) VALUES (%s, %s, %s, %s, %s)",
        (username, card_no, datetime.now(), money, r_id)
    )
    db.close()
    print("添加成功!")


# 通用查询函数
def query(sql, params=None):
    db = connect()
    cursor = db.cursor()
    cursor.execute(sql, params)
    result = cursor.fetchall()
    db.close()
    return result


# 显示结果
def show_results(rows, title):
    print(f"\n===== {title} =====")
    if not rows:
        print("没有找到记录!")
        return

    headers = ["ID", "用户名", "卡号", "开卡时间", "余额", "银行"]
    for row in rows:
        print(f"{row[0]}\t{row[1]}\t{row[2]}\t{row[3]}\t{row[4]}\t{row[5]}")


# 功能函数
def findAll():
    rows = query("""
                SELECT c.id, c.username, c.card_no, c.create_time, c.money, r.r_name
                FROM t_card c
                JOIN t_rank r ON c.r_id = r.id
                ORDER BY c.id
                 """)
    show_results(rows, "所有银行卡信息")


def add_card_interactive():
    username = input("请输入用户名: ")
    card_no = input("请输入卡号: ")
    money = float(input("请输入初始金额: "))

    # 显示银行列表
    banks = query("SELECT id, r_name FROM t_rank")
    for bank in banks:
        print(f"{bank[0]}. {bank[1]}")

    r_id = int(input("请选择银行ID: "))
    add_card(username, card_no, money, r_id)


def update_card_money():
    card_id = int(input("请输入银行卡ID: "))
    new_money = float(input("请输入新金额: "))

    db = connect()
    cursor = db.cursor()
    # 执行UPDATE语句
    affected_rows = cursor.execute("UPDATE t_card SET money = %s WHERE id = %s", (new_money, card_id))
    db.close()

    if affected_rows > 0:
        print("修改成功!")
    else:
        print("未找到银行卡!")


def find_max_balance():
    row = query("""
                SELECT c.id, c.username, c.card_no, c.create_time, c.money, r.r_name
                FROM t_card c
                JOIN t_rank r ON c.r_id = r.id
                ORDER BY c.money DESC LIMIT 1
                """)
    show_results(row, "余额最多的银行卡")


def find_cards_by_bank():
    banks = query("SELECT id, r_name FROM t_rank")
    for bank in banks:
        print(f"{bank[0]}. {bank[1]}")

    bank_id = int(input("请选择银行ID: "))
    rows = query("SELECT card_no, money FROM t_card WHERE r_id = %s", (bank_id,))

    total = sum(row[1] for row in rows)
    for row in rows:
        print(f"卡号: {row[0]}, 余额: {row[1]}")
    print(f"总余额: {total}")


def find_cards_by_balance_range():
    min_bal = float(input("最小余额: "))
    max_bal = float(input("最大余额: "))

    rows = query("""
                SELECT c.id, c.username, c.card_no, c.create_time, c.money, r.r_name
                FROM t_card c
                JOIN t_rank r ON c.r_id = r.id
                WHERE c.money BETWEEN %s AND %s
                ORDER BY c.money
                 """, (min_bal, max_bal))
    show_results(rows, f"余额范围 {min_bal}-{max_bal} 的银行卡")


def find_cards_by_date_range():
    start = input("开始日期(YYYY-MM-DD): ")
    end = input("结束日期(YYYY-MM-DD): ")

    rows = query("""
                SELECT c.id, c.username, c.card_no, c.create_time, c.money, r.r_name
                FROM t_card c
                JOIN t_rank r ON c.r_id = r.id
                WHERE c.create_time BETWEEN %s AND %s
                ORDER BY c.create_time
                 """, (start, end + " 23:59:59"))
    show_results(rows, f"开卡日期 {start} 到 {end} 的银行卡")


def find_cards_by_name():
    name = input("请输入姓名: ")
    rows = query("""
                SELECT c.id, c.username, c.card_no, c.create_time, c.money, r.r_name
                FROM t_card c
                JOIN t_rank r ON c.r_id = r.id
                WHERE c.username LIKE %s
                ORDER BY c.username
                 """, (f"%{name}%",))
    show_results(rows, f"姓名包含 '{name}' 的银行卡")


# 主菜单
def main_menu():
    while True:
        print("\n银行卡管理系统")
        print("1.浏览所有银行卡 2.添加银行卡 3.修改金额")
        print("4.余额最多卡 5.按银行查询 6.按余额查询")
        print("7.按日期查询 8.按姓名查询 0.退出")

        choice = input("请选择: ")
        options = {
            '1': findAll, '2': add_card_interactive, '3': update_card_money,
            '4': find_max_balance, '5': find_cards_by_bank, '6': find_cards_by_balance_range,
            '7': find_cards_by_date_range, '8': find_cards_by_name, '0': exit
        }

        if choice in options:
            if choice == '0':
                print("再见!")
                break
            options[choice]()
        else:
            print("无效选择!")


# 启动
if __name__ == '__main__':
    createTable()
    main_menu()